package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.jhc.bean.NewsBean;

public class NewsDAO {
	
	private Connection con = null;
	int PageCount; 
	
	public NewsBean getContext(int id){
		NewsBean s = new NewsBean();
		String sql = "select * from news where id = ?";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				s = null;
			else {
				s.setId(result.getInt(1));
				s.setTitle(result.getString(2));
				s.setContext(result.getString(3));
				s.setDate(result.getDate(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}
	public ArrayList<NewsBean> getAllNews(int page){
		ArrayList<NewsBean> list = new ArrayList<NewsBean>();
		String sql = "select a.name,n.id,n.title,n.context,n.date "
				+ "from adminuser as a join news as n on a.id = n.user_id order by n.id desc limit "
				+ (page-1) * (10) + "," + 10 + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while(result.next()) {
				NewsBean s = new NewsBean();
				s.setName(result.getString(1));
				s.setId(result.getInt(2));
				s.setTitle(result.getString(3));
				s.setContext(result.getString(4));
				s.setDate(result.getDate(5));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public int getCount() {
		String sql = "select count(*) from adminuser as a join news as n on a.id = n.user_id";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%10==0){
					this.PageCount=(result.getInt(1)/10);	
				}else{
					this.PageCount=(result.getInt(1)/10+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}	
	public void addNews(int userid, String title, String content) {
		String sql = "insert into news(user_id,title,context,date) "
				+ "values(?,?,?,now())";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.setString(2, title);
			st.setString(3, content);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void deleteNews(int id) {
		String sql = "delete from news where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void setCon(Connection con) {
		this.con = con;
	}
}
